package recommenRatings.data;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.LinkedList;
import java.util.List;

import recommenRatings.domain.Opinion;
import recommenRatings.domain.Photo;
import recommenRatings.domain.Place;

public class JDBCPlaceDAO implements IPlaceDAO {

	public void delete(Connection conn, String placeOID) {
		String sql = "DELETE FROM Places WHERE (OID = ?) ";
		PreparedStatement stmt = null;

		try {
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, placeOID);
			stmt.executeUpdate();
		} catch (SQLException e) {
			System.err.println("Message: " + e.getMessage());
			System.err.println("SQLState: " + e.getSQLState());
			System.err.println("ErrorCode: " + e.getErrorCode());
		} finally {
			try {
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
			}
		}
	}

	public void insert(Connection conn, Place p) {
		String sql = "INSERT INTO Places ( placePOID, name, address, phoneNumber, fax, web, type, subType, state, city, price) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ";
		PreparedStatement stmt = null;

		try {
			stmt = conn.prepareStatement(sql);

			stmt.setString(1, p.getPlaceID());
			stmt.setString(2, p.getName());
			stmt.setString(3, p.getAddress());
			stmt.setString(4, p.getPhoneNumber());
			stmt.setString(5, p.getFax());
			stmt.setString(6, p.getWeb());
			stmt.setString(7, p.getType());
			stmt.setString(8, p.getSubType());
			stmt.setString(9, p.getState());
			stmt.setString(10, p.getCity());
			stmt.setString(11, p.getPrice());

			stmt.executeUpdate();

		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			try {
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException e) {
			}
		}

	}

	public List<Place> select(String statement) {

		Connection conn = ConnectionManager.getInstance().checkOut();

		String sql = "SELECT * FROM Places";
		PreparedStatement stmt = null;
		ResultSet result = null;
		List<Place> searchResults = new LinkedList<Place>();

		try {
			sql = sql + statement;
			stmt = conn.prepareStatement(sql);

			result = stmt.executeQuery();

			while (result.next()) {
				Place p = null;
				p = new Place();
				p.setPlaceID(result.getString("placePOID"));
				p.setName(result.getString("name"));
				p.setAddress(result.getString("address"));
				p.setPhoneNumber(result.getString("phoneNumber"));
				p.setFax(result.getString("fax"));
				p.setWeb(result.getString("web"));
				p.setType(result.getString("type"));
				p.setSubType(result.getString("subType"));
				p.setState(result.getString("state"));
				p.setCity(result.getString("city"));
				p.setPrice(result.getString("price"));

				List<Opinion> lo = (new JDBCOpinionDAO()).select(conn, result
						.getString("placePOID"));

				p.setOpinions(lo);

				List<Photo> lp = (new JDBCPhotoDAO()).select(conn, result
						.getString("placePOID"));

				p.setPhotos(lp);

				searchResults.add(p);

			}

		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			ConnectionManager.getInstance().checkIn(conn);
			try {
				if (result != null) {
					result.close();
				}
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException e) {
			}
		}

		return searchResults;

	}

}
